package cn.fintecher.gis.respository;

import cn.fintecher.gis.entity.LandDisaster;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

public interface LandDisasterRepository extends QuerydslPredicateExecutor<LandDisaster>, JpaRepository<LandDisaster, String>{

    @Query(value = "select h.district_name,count(*) from (select b.district_name,a.id,a.create_time from land_disaster a ,district_info b " +
            "where a.prefecture=b.id) h GROUP BY h.district_name", nativeQuery = true)
    public Object[] getStatisticalName();

    @Query(value = "select h.susceptible,count(*) from (select a.susceptible,a.id,a.create_time from land_disaster a ) h GROUP BY h.susceptible", nativeQuery = true)
    public Object[] getSusceptible();

    @Query(value = "select grade,count(*) from land_disaster where prefecture in(select id from district_info where district_name=:prefecture) GROUP BY grade", nativeQuery = true)
    public Object[] getStatisticalByGrade(@Param("prefecture") String prefecture);


    @Query(value = "select grade,count(*) from land_disaster where susceptible=:susceptible GROUP BY grade", nativeQuery = true)
    public Object[] getSusceptibleByGrade(@Param("susceptible") String susceptible);

}
